package net.jsoft.pms.mapper.scs;

import java.text.MessageFormat;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import net.jsoft.pms.vo.scs.PublishService;
import net.jsoft.pms.vo.scs.Service;

public class ServicesListProvider
{
    public String findByPageAndParms(String ywfwid, String ywfwmc, String type)
    {
        String sql = "SELECT * FROM  hx_sjmx.DZSWJ_YWFWQDB WHERE REQUESTXSD IS NOT NULL AND RESPONSEXSD IS NOT NULL  AND  DWFBZT IN(0,1,2,3,4,5,6)   ";
        if (StringUtils.isNotEmpty(ywfwid))
        {
            sql += "  AND YWFWID LIKE '" + ywfwid + "' ";
        }
        if (StringUtils.isNotEmpty(ywfwmc))
        {
            sql += "  AND YWFWMC LIKE '%" + ywfwmc + "%' ";
        }
        if (StringUtils.isNotEmpty(type))
        {
            sql += "  AND DWFBZT = '" + type + "' ";
        }
        sql += " ORDER BY SSZB DESC ";
        return sql;
    }

    public String findFromDate(String date)
    {
        String sql = "SELECT * FROM hx_sjmx.DZSWJ_YWFWQDB WHERE  REQUESTXSD IS NOT NULL AND RESPONSEXSD IS NOT NULL AND  DWFBZT IN(0,1,2,3,4,5,6) ";
        if (StringUtils.isNotEmpty(date))
        {
            sql += "  AND XGRQ >=  to_date('" + date + "','YYYY-MM-DD HH24:MI:SS')";
        }
        sql += " ORDER BY SSZB  DESC  ";
        return sql;
    }

    public String insertService(List<Service> list, PublishService publishService, String type)
    {

        StringBuilder sb = new StringBuilder();
        sb.append("INSERT INTO HX_ZGXT.DZSWJ_ZHGLXT_SERVICELIST(ID,SERVICEID,PUBLISHED_FLAG,PUBLISHED_VERSION,EDIT_NOTE,INFLUENCE,CRUD_TYPE,MAINID )  ");
        //        sb.append("VALUES ");
        sb.append(" ");
        MessageFormat mf = new MessageFormat(
                "sys_guid(), #'{'list[{0}].ywfwid}, #'{'type},#'{'publishService.version},#'{'list[{0}].editNote,jdbcType=VARCHAR},#'{'list[{0}].influence,jdbcType=VARCHAR},#'{'list[{0}].dwfbzt},#'{'publishService.id}");
        for (int i = 0; i < list.size(); i++)
        {
            sb.append(" (SELECT ");
            sb.append(mf.format(new Object[]
            { i }));
            sb.append(" FROM DUAL ");
            if (i < list.size() - 1)
            {
                sb.append(") UNION ALL ");
            }
            else
            {
                sb.append(" ) ");
            }
        }
        return sb.toString();
    }

}
